import datetime
import time
import pandas as pd
import numpy as np
import Core.Gadget as Gadget
import Core.MySQLDB as MySQLDB
import os
import xlrd
import re
import math
import matplotlib.pyplot as plt

def Create(name):
    pass

def LoadFolder(database, path, beginDateTime=datetime.datetime(2000,1,1)):
    #
    documents = []
    fileList = os.listdir(path)
    for fileName in fileList:
        fullPathFilename = os.path.join(path, fileName)
        fmodifytime = os.stat(fullPathFilename).st_mtime
        datetime1 = datetime.datetime.fromtimestamp(fmodifytime)
        # file_modify_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(mtime))
        # print("{0} 修改时间是: {1}".format(full_path, file_modify_time))

        # 忽略太旧文件
        if datetime1 < beginDateTime:
            continue
        #
        print(fullPathFilename)
        document = ReadFile(fullPathFilename)
        documents.append(document)

    #
    SaveToDatabase(database, documents)
    a = 0


def ReadFile(fullPathFilename):

    def FindFirstValid(content, target=""):
        for data in content:
            if target != "" and target in data:
                return data
            if target == "" and data != "":
                return data
        return ""

    # 找到产品名称
    def ParseName(rawName):
        #
        names = rawName.split("___")
        if len(names) == 1:
            names = rawName.split("__")
        if len(names) == 1:
            names = rawName.split("_")
        #
        if len(names) == 1:
            name = rawName
        else:
            name = names[1]
        #
        # pattern = "^([a-zA-Z]{1,5})([0-9]{3,4})([-S]*)"
        # b = re.match(pattern, a[0])
        return name

    # Process DateTime
    def ParseDateTime(strDateTime):
        datetimes = strDateTime.split("：")
        if len(datetimes) == 1:
            datetimes = strDateTime.split(":")
        #
        updateDateTime = None
        if "-" in strDateTime:
            sformat = "%Y-%m-%d"
        else:
            sformat = "%Y%m%d"

        s = datetimes[1].strip()
        updateDateTime = datetime.datetime.strptime(s, sformat)
        return updateDateTime

    wb = xlrd.open_workbook(filename=fullPathFilename)
    sheet = wb.sheet_by_index(0)#通过索引获取表格
    #
    nrows = sheet.nrows
    ncols = sheet.ncols
    i = 0
    headerIndexByName = {}
    table = []
    fieldIndexByName = {}
    fieldCount = 0
    name = None
    updateDateTime = None
    located = -1
    for i in range(0, nrows):
        row_data = sheet.row_values(i)
        content = row_data
        #
        validString = FindFirstValid(content)
        if located < 0 and "科目代码" in validString: # Process Headers
            located = i
            headerCount = 0
            for header in content:
                headerIndexByName[header] = headerCount
                headerCount = headerCount + 1

        # Process Content
        if len(headerIndexByName) != 0:
            # Process Contents
            entry = []
            for data in content:
                # data = data.strip("\n")
                entry.append(data)
            table.append(entry)
            #
            fieldIndexByName[validString] = fieldCount
            fieldCount += 1
    #
    # Process Name
    if located > 0:
        tempData = sheet.row_values(located - 2)
        validString = FindFirstValid(tempData)
        name = ParseName(validString)
        print("Process", name)
        #
        tempData = sheet.row_values(located - 1)
        validString = FindFirstValid(tempData, "日期")
        updateDateTime = ParseDateTime(validString)
        print(updateDateTime)
        #

    for key, value in headerIndexByName.items():
        if key == "市值" or key=="市值-本币":
            x = value
        if "科目名称" in key:
            x2 = value
    #
    document = {}
    document["PortfolioName"] = name
    document["DateTime"] = updateDateTime

    for key, value in fieldIndexByName.items():
        # print(key, value, table[value][x2])
        if "资产净值" in key:
            netAsset = table[value][x]
            document["NetAsset"] = netAsset
            print("NetAsset", netAsset)
        if "基金单位净值" in key or "今日单位净值" in key or key == "单位净值":
            netValue = table[value][x2]
            document["UnitNetValue"] = netValue
            print("UnitNetValue", netValue)
        if "累计单位净值" in key:
            cumNetValue = table[value][x2]
            document["CumNetValue"] = cumNetValue
            print("CumNetValue", cumNetValue)
    print("")
    #
    return document


def SaveToDatabase(database, documents):
    #
    print("Saving To Database")
    for document in documents:
        document["Date"] = document["DateTime"].date()
        document["Key2"] = document["PortfolioName"] + "_" + Gadget.ToDateString(document["DateTime"])
    #
    database.Upsert_Many("Portfolio", "Trading_portfolio", {}, documents)


def SyncDate(database, portfolioName, datetime1, datetime2):
    #
    pfFilter = {"PortfolioName": portfolioName}
    pfFilter["Date"] = {">=": datetime1, "<=": datetime2}
    documents = database.Find("Portfolio", "Trading_Portfolio", pfFilter, sort=[("Date", 1)])
    df = Gadget.DocumentsToDataFrame(documents, keep=["Date", "NetAsset", "UnitNetValue", "CumNetValue"])
    #
    firstDate = df.iloc[0]["Date"]
    lastDate = df.iloc[-1]["Date"]

    #
    bmFilter = {"Symbol": "000001.SH"}
    bmFilter["Date"] = {">=": firstDate, "<=": lastDate}
    bmDocuments = database.Find("DailyBar", "Index", filter=bmFilter, sort=[("Date", 1)])
    dfBM = Gadget.DocumentsToDataFrame(bmDocuments, keep=["Date", "Close"])
    df = pd.merge(dfBM, df, how="left", on="Date")
    # print(df)

    return df



def Stats(database, path):
    #
    plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
    plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
    #
    maxDate = datetime.date(2000,1,1)
    portfolioList = database.ExecuteSQL("portfolio", "SELECT distinct(PortfolioName) FROM portfolio.trading_portfolio")

    results = []
    for p in portfolioList:
        name = p[0]
        print("Stat", name)
        #
        documents = database.Find("Portfolio", "Trading_Portfolio", {"PortfolioName": name}, sort=[("Date", 1)])
        df = Gadget.DocumentsToDataFrame(documents, keep=["Date", "NetAsset", "UnitNetValue", "CumNetValue"])
        # print(df)
        #
        df["DailyReturn"] = df["UnitNetValue"] / df["UnitNetValue"].shift(1) - 1
        df["LogDailyReturn"] = np.log(df["UnitNetValue"] / df["UnitNetValue"].shift(1))
        #
        annulizdFactor = 252
        annulizdRet = df["DailyReturn"].mean() * annulizdFactor
        sd = df["DailyReturn"].std()
        annulizdSd = sd * math.sqrt(annulizdFactor)
        #
        sharpe = annulizdRet / annulizdSd
        #
        lastMoney = df.iloc[-1]["NetAsset"]
        firstDate = df.iloc[0]["Date"]
        lastDate = df.iloc[-1]["Date"]
        #
        bmFilter = {"Symbol": "000001.SH"}
        bmFilter["Date"] = {">=": firstDate, "<=": lastDate}
        bmDocuments = database.Find("DailyBar", "Index", filter=bmFilter, sort=[("Date", 1)])
        dfBM = Gadget.DocumentsToDataFrame(bmDocuments, keep=["Date", "Close"])
        df = pd.merge(dfBM, df, how="left", on="Date")
        # print(df)
        dfMissing = df[pd.isna(df['NetAsset']) == True]
        if len(dfMissing) > 0:
            print("Possible Missing")
            print(dfMissing)
        # print(df[pd.isna(df['NetAsset']) == True])
        # print(df[pd.isnull(df['NetAsset']) == True])
        # print(df[np.isnan(df['NetAsset']) == True])

        if lastDate > maxDate:
            maxDate = lastDate

        #
        VARRatio = -2.33 * sd
        VARMoney = VARRatio * lastMoney
        #
        resultDocument = {}
        resultDocument["产品名称"] = name
        resultDocument["更新日期"] = lastDate
        resultDocument["规模"] = lastMoney
        resultDocument["存续时间"] = (lastDate - firstDate).days
        #
        resultDocument["单位净值"] = df.iloc[-1]["UnitNetValue"]
        resultDocument["累计净值"] = df.iloc[-1]["CumNetValue"]
        resultDocument["年化收益"] = annulizdRet
        resultDocument["年化波动"] = annulizdSd
        resultDocument["Sharpe"] = sharpe
        resultDocument["日度VAR99%"] = VARRatio
        resultDocument["日度VAR99%金额"] = VARMoney
        #
        results.append(resultDocument)
        #
        df.plot(x="Date", y=["UnitNetValue"], grid=True, title=name)

        # results.Benchmark.plot(ax=ax1)
        # ax1.set_ylabel('Net Unit Value')
        # # ax1.set_xlabel('DateTime')
        # #
        # # ax2 = plt.subplot(212, sharex=ax1)
        # ax2 = plt.subplot(212)
        # results.CumExcessReturn.plot(ax=ax2)
        # ax2.set_ylabel('Cumulative Excess Return')

        # Show the plot.
        # plt.gcf().set_size_inches(18, 8)
        plt.savefig(path + "/" + name + "_performances.png")
        # plt.show()
    #
    df_result = Gadget.DocumentsToDataFrame(results)
    # df_result.to_csv(path + "/台账管理_" + Gadget.ToDateString(maxDate) + ".csv")
    df_result.to_excel(path + "/台账管理_" + Gadget.ToDateString(maxDate) + ".xls")


def CalculateVAR():
    pass


if __name__ == '__main__':

    # ---Connecting Database---
    database = MySQLDB.MySQLDB(address="10.13.144.134",
                               port="3306",
                               username="associate",
                               password="123456")

    # LoadFolder(database, "d:/估值表/估值表1113/")
    # LoadFolder(database, "d:/TradeLog/青骓全球/")
    LoadFolder(database, "d:/估值表/0930/")
    #
    # Stats(database, "D:/台账管理")